/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models.DAO;

/**
 *
 * @author Don
 */
import DBConnection.ConnectionFactory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import Models.Admin;
import Models.ECA;
import Models.Member;

public class Admin_DAO {
    
    public boolean checkExists(int memberid){
            try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member WHERE memberid = ?");
             
             int i = 1;
             pstmt.setInt(i++, memberid);
             ResultSet rs = pstmt.executeQuery();
             
             if(rs.next()){
             return true;
             }
             
             return false;
         }        
         catch(Exception e){
         System.out.print(e);
            return false;
         }
    }
    
    public ArrayList<Member> getAllMembers()
     {
         System.out.print("getAllMembers");
         Member member;
         ArrayList<Member> memberList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member ORDER BY lastname ASC");
             
             int i = 1;
           
             ResultSet rs = pstmt.executeQuery();
             
             memberList = new ArrayList<Member>();
             
             while(rs.next())
             {
                 member = new Member();
                 member.setMemberid(rs.getInt("memberid"));
                 member.setFirstname(rs.getString("firstname"));
                 
                 member.setLastname(rs.getString("lastname"));
                 
                 member.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 member.setMemberid(rs.getInt("memberid"));
                 
                 member.setCourse(rs.getString("course"));
                 
                 member.setBatch(rs.getString("batch"));
                 member.setStatus(rs.getString("status"));
                 memberList.add(member);
             }
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
    
    
    public ArrayList<Member> getAllAttendees(String ecaidstring)
     {
         System.out.print("getAllMembers");
         Member member;
         ArrayList<Member> memberList = null;
         int ecaid = Integer.parseInt(ecaidstring);
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM eca_attendance WHERE ecaid = ? and deletestatus = 0");
             PreparedStatement pstmt_member = conn.prepareStatement("SELECT * FROM member WHERE memberid = ?");

             int i = 1;
             pstmt.setInt(i++, ecaid);
             ResultSet rs = pstmt.executeQuery();
             memberList = new ArrayList<Member>();
             

             
             while(rs.next())
             {
                 int memid = rs.getInt("memberid");
                 int j = 1;
                 
                 
                 pstmt_member.setInt(j++,memid);
                 ResultSet rs2 = pstmt_member.executeQuery();
                 
                while(rs2.next())
                {
                    member = new Member();
                    member.setMemberid(rs2.getInt("memberid"));
                    member.setFirstname(rs2.getString("firstname"));

                    member.setLastname(rs2.getString("lastname"));

                    member.setFullname(rs2.getString("firstname") + " " + rs2.getString("lastname"));
                    member.setMemberid(rs2.getInt("memberid"));

                    member.setCourse(rs2.getString("course"));

                    member.setBatch(rs2.getString("batch"));
                    member.setStatus(rs2.getString("status"));
                    memberList.add(member);
                }
             
             }
             
             
             
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
    
    
    public ArrayList<ECA> getSingleActivity(String ecaidstring)
     {
         System.out.print("getAllMembers");
         ECA eca;
         ArrayList<ECA> ecaList = null;
         int ecaid = Integer.parseInt(ecaidstring);
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt_actName = conn.prepareStatement("SELECT * FROM extra_curricular_activities WHERE ecaid =?");
             
             
             int i = 1;
             pstmt_actName.setInt(i++,ecaid);
             ResultSet rs = pstmt_actName.executeQuery();
             

             ecaList = new ArrayList<ECA>();
             
             while(rs.next())
             {
                 eca = new ECA();
   
                 eca.setEcaid(rs.getInt("ecaid"));
                 eca.setName(rs.getString("name"));
                 eca.setDate(rs.getDate("date"));
                 eca.setTime(rs.getTime("time"));
                 eca.setPoints(rs.getFloat("points"));
                 eca.setEcaClassID(rs.getInt("ecaClassID"));
                 eca.setStatus(rs.getString("status"));
        
                 ecaList.add(eca);
             
             }
             
             
             
             System.out.print(ecaList);
             return ecaList;
         }        
         catch(Exception e){
         System.out.print(e);
            return ecaList;
         }
         finally{
             return ecaList;
         }
         
         
     }
    
    public ArrayList<Member> getAllMembersReport(int id)
     {
         System.out.print("getAllMembers");
         Member member;
         ArrayList<Member> memberList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member where memberid = ? ORDER BY lastname ASC");
             
             int i = 1;
             pstmt.setInt(i++, id);
             ResultSet rs = pstmt.executeQuery();
             
             memberList = new ArrayList<Member>();
             
             while(rs.next())
             {
                 member = new Member();
                 member.setMemberid(rs.getInt("memberid"));
                 member.setFirstname(rs.getString("firstname"));
                 
                 member.setLastname(rs.getString("lastname"));
                 
                 member.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 member.setMemberid(rs.getInt("memberid"));
                 
                 member.setCourse(rs.getString("course"));
                 
                 member.setBatch(rs.getString("batch"));
                 member.setStatus(rs.getString("status"));
                 memberList.add(member);
             }
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
    
    
    public ArrayList<Member> getAllBatches()
     {
         System.out.print("getAllBatches");
         Member member;
         ArrayList<Member> memberList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT DISTINCT batch FROM member ORDER BY batch ASC");
             
             int i = 1;
           
             ResultSet rs = pstmt.executeQuery();
             
             memberList = new ArrayList<Member>();
             
             while(rs.next())
             {
                 member = new Member();

                 member.setBatch(rs.getString("batch"));

                 memberList.add(member);
             }
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
    
    
    public ArrayList<Member> getAllBatchesActive(int batch)
     {
         System.out.print("getAllMembers");
         Member member;
         ArrayList<Member> memberList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member WHERE batch = ? ORDER BY lastname ASC");
             
             int i = 1;
             pstmt.setInt(i++, batch);
             ResultSet rs = pstmt.executeQuery();
             
             memberList = new ArrayList<Member>();
             
             while(rs.next())
             {
                 member = new Member();
   
                 member.setFirstname(rs.getString("firstname"));
                 
                 member.setLastname(rs.getString("lastname"));
                 
                 member.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 member.setMemberid(rs.getInt("memberid"));
                 
                 member.setCourse(rs.getString("course"));
                 
                 member.setBatch(rs.getString("batch"));
                 member.setStatus(rs.getString("status"));
                 memberList.add(member);
             }
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
    
    public ArrayList<Member> getAllMembersActive()
     {
         System.out.print("getAllMembers");
         Member member;
         ArrayList<Member> memberList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member where status = 'Active' ORDER BY lastname ASC ");
             
             int i = 1;
           
             ResultSet rs = pstmt.executeQuery();
             
             memberList = new ArrayList<Member>();
             
             while(rs.next())
             {
                 member = new Member();
   
                 member.setFirstname(rs.getString("firstname"));
                 
                 member.setLastname(rs.getString("lastname"));
                 
                 member.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 member.setMemberid(rs.getInt("memberid"));
                 
                 member.setCourse(rs.getString("course"));
                 
                 member.setBatch(rs.getString("batch"));
                 member.setStatus(rs.getString("status"));
                 memberList.add(member);
             }
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
    
    public ArrayList<Member> getAllMembersInactive()
     {
         System.out.print("getAllMembers");
         Member member;
         ArrayList<Member> memberList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member where status = 'Inactive' ORDER BY lastname ASC");
             
             int i = 1;
           
             ResultSet rs = pstmt.executeQuery();
             
             memberList = new ArrayList<Member>();
             
             while(rs.next())
             {
                 member = new Member();
   
                 member.setFirstname(rs.getString("firstname"));
                 
                 member.setLastname(rs.getString("lastname"));
                 
                 member.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 member.setMemberid(rs.getInt("memberid"));
                 
                 member.setCourse(rs.getString("course"));
                 
                 member.setBatch(rs.getString("batch"));
                 member.setStatus(rs.getString("status"));
                 memberList.add(member);
             }
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
    
    public ArrayList<Member> getAllMembersProbation()
     {
         System.out.print("getAllMembers");
         Member member;
         ArrayList<Member> memberList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member where status = 'Probation' ORDER BY lastname ASC");
             
             int i = 1;
           
             ResultSet rs = pstmt.executeQuery();
             
             memberList = new ArrayList<Member>();
             
             while(rs.next())
             {
                 member = new Member();
   
                 member.setFirstname(rs.getString("firstname"));
                 
                 member.setLastname(rs.getString("lastname"));
                 
                 member.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 member.setMemberid(rs.getInt("memberid"));
                 
                 member.setCourse(rs.getString("course"));
                 
                 member.setBatch(rs.getString("batch"));
                 member.setStatus(rs.getString("status"));
                 memberList.add(member);
             }
             System.out.print(memberList);
             return memberList;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberList;
         }
         finally{
             return memberList;
         }
         
         
     }
    
    
    public ArrayList<ECA> getAllECArecognized()
     {
         System.out.print("getAllECA");
         ECA eca;
         ArrayList<ECA> ecaList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM extra_curricular_activities where status ='Recognized' and deletestatus = 0");
             
             int i = 1;
           
             ResultSet rs = pstmt.executeQuery();
             
             ecaList = new ArrayList<ECA>();
             
             while(rs.next())
             {
                 eca = new ECA();
   
                 eca.setEcaid(rs.getInt("ecaid"));
                 eca.setName(rs.getString("name"));
                 eca.setDate(rs.getDate("date"));
                 eca.setTime(rs.getTime("time"));
                 eca.setPoints(rs.getFloat("points"));
                 eca.setEcaClassID(rs.getInt("ecaClassID"));
                 eca.setStatus(rs.getString("status"));

                 
                 ecaList.add(eca);
             }
             System.out.print(ecaList);
             return ecaList;
         }        
         catch(Exception e){
         System.out.print(e);
            return ecaList;
         }
         finally{
             return ecaList;
         }
         
         
     }
    
    public ArrayList<ECA> getAllECAName(String ecaname)
     {
         System.out.print("getAllECA");
         ECA eca;
         ArrayList<ECA> ecaList = null;
         
         try{
             ecaname.toLowerCase();
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM extra_curricular_activities where LCASE(name) LIKE ?");
             
             int i = 1;
             pstmt.setString(i++, "%" + ecaname + "%");
             ResultSet rs = pstmt.executeQuery();
             
             ecaList = new ArrayList<ECA>();
             
             while(rs.next())
             {
                 eca = new ECA();
   
                 eca.setEcaid(rs.getInt("ecaid"));
                 eca.setName(rs.getString("name"));
                 eca.setDate(rs.getDate("date"));
                 eca.setTime(rs.getTime("time"));
                 eca.setPoints(rs.getFloat("points"));
                 eca.setEcaClassID(rs.getInt("ecaClassID"));
                 eca.setStatus(rs.getString("status"));

                 
                 ecaList.add(eca);
             }
             System.out.print(ecaList);
             return ecaList;
         }        
         catch(Exception e){
         System.out.print(e);
            return ecaList;
         }
         finally{
             return ecaList;
         }
         
         
     }
    
    public ArrayList<ECA> getAllECAUnrecognized()
     {
         System.out.print("getAllECA");
         ECA eca;
         ArrayList<ECA> ecaList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM extra_curricular_activities where status ='Unrecognized' and deletestatus = 0");
             
             int i = 1;
           
             ResultSet rs = pstmt.executeQuery();
             
             ecaList = new ArrayList<ECA>();
             
             while(rs.next())
             {
                 eca = new ECA();
   
                 eca.setEcaid(rs.getInt("ecaid"));
                 eca.setName(rs.getString("name"));
                 eca.setDate(rs.getDate("date"));
                 eca.setTime(rs.getTime("time"));
                 eca.setPoints(rs.getFloat("points"));
                 eca.setEcaClassID(rs.getInt("ecaClassID"));
                 eca.setStatus(rs.getString("status"));

                 
                 ecaList.add(eca);
             }
             System.out.print(ecaList);
             return ecaList;
         }        
         catch(Exception e){
         System.out.print(e);
            return ecaList;
         }
         finally{
             return ecaList;
         }
         
         
     }
    
    public ArrayList<ECA> getAllECArecognizedSortAZ()
     {
         System.out.print("getAllECA");
         ECA eca;
         ArrayList<ECA> ecaList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM extra_curricular_activities where status ='Recognized' and deletestatus = 0 ORDER BY name ASC");
             
             int i = 1;
           
             ResultSet rs = pstmt.executeQuery();
             
             ecaList = new ArrayList<ECA>();
             
             while(rs.next())
             {
                 eca = new ECA();
   
                 eca.setEcaid(rs.getInt("ecaid"));
                 eca.setName(rs.getString("name"));
                 eca.setDate(rs.getDate("date"));
                 eca.setTime(rs.getTime("time"));
                 eca.setPoints(rs.getFloat("points"));
                 eca.setEcaClassID(rs.getInt("ecaClassID"));
                 eca.setStatus(rs.getString("status"));

                 
                 ecaList.add(eca);
             }
             System.out.print(ecaList);
             return ecaList;
         }        
         catch(Exception e){
         System.out.print(e);
            return ecaList;
         }
         finally{
             return ecaList;
         }
         
         
     }
    
    public ArrayList<ECA> getAllECArecognizedSortDate()
     {
         System.out.print("getAllECA");
         ECA eca;
         ArrayList<ECA> ecaList = null;
         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM extra_curricular_activities where status ='Recognized' and deletestatus = 0 ORDER BY date ASC");
             
             int i = 1;
           
             ResultSet rs = pstmt.executeQuery();
             
             ecaList = new ArrayList<ECA>();
             
             while(rs.next())
             {
                 eca = new ECA();
   
                 eca.setEcaid(rs.getInt("ecaid"));
                 eca.setName(rs.getString("name"));
                 eca.setDate(rs.getDate("date"));
                 eca.setTime(rs.getTime("time"));
                 eca.setPoints(rs.getFloat("points"));
                 eca.setEcaClassID(rs.getInt("ecaClassID"));
                 eca.setStatus(rs.getString("status"));

                 
                 ecaList.add(eca);
             }
             System.out.print(ecaList);
             return ecaList;
         }        
         catch(Exception e){
         System.out.print(e);
            return ecaList;
         }
         finally{
             return ecaList;
         }
         
         
     }
    
    public ECA getECA(String ecaid)
     {
         System.out.print("getECA");
         ECA eca = null;

         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM extra_curricular_activities where ecaid = ? and deletestatus = 0");
             
             int i = 1;
             pstmt.setInt(i++, Integer.valueOf(ecaid));
             
             ResultSet rs = pstmt.executeQuery();
             
             
             while(rs.next())
             {
                 eca = new ECA();
   
                 eca.setEcaid(rs.getInt("ecaid"));
                 eca.setName(rs.getString("name"));
                 eca.setDate(rs.getDate("date"));
                 eca.setTime(rs.getTime("time"));
                 eca.setPoints(rs.getFloat("points"));
                 eca.setEcaClassID(rs.getInt("ecaClassID"));
                 eca.setStatus(rs.getString("status"));


             }

             return eca;
         }        
         catch(Exception e){
         System.out.print(e);
            return eca;
         }
         finally{
             return eca;
         }
         
         
     }
    
    
    
    public boolean addECA(ECA eca){
        try{

            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO extra_curricular_activities(name, date, time, points) VALUES(?,?,?,?)");
            pstmt.setString(i++, eca.getName());
            pstmt.setDate(i++, eca.getDate());
            pstmt.setTime(i++, eca.getTime());
            pstmt.setFloat(i++, eca.getPoints());
            
            pstmt.executeUpdate();

            return true;
        }
        catch(Exception e){
            System.out.print(e);
            return false;
        }
    }
    
    public boolean editECA(ECA eca){
        try{
            
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            
            PreparedStatement pstmt = conn.prepareStatement("UPDATE extra_curricular_activities SET name = ?, date = ? , time = ?, points = ?, status = ? WHERE ecaid = ?");
            pstmt.setString(i++, eca.getName()); 
            pstmt.setDate(i++, eca.getDate());
            pstmt.setTime(i++, eca.getTime());
            pstmt.setFloat(i++, eca.getPoints());
            pstmt.setString(i++, eca.getStatus());
            pstmt.setInt(i++, eca.getEcaid());
            
            pstmt.executeUpdate();
            
            
            
            return true;
        }
        catch(Exception e){
            System.out.print(e);
            return false;
        }
    }
    
    public boolean deleteECA(int ecaID){
        try{
            
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1; int j = 1;
            
            PreparedStatement pstmt= conn.prepareStatement("UPDATE extra_curricular_activities SET deletestatus = ? WHERE ecaid = ?");
            pstmt.setInt(i++, 1);
            pstmt.setInt(i++, ecaID);
            pstmt.executeUpdate();
            
            
            return true;
        }
        catch(Exception e){
            return false;
        }
    }
    
    public boolean deleteMemberFromECA(String ecaIDstring, int memberid){
        try{
            
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1; int j = 1;
            int ecaID = Integer.parseInt(ecaIDstring);
            
            PreparedStatement pstmt= conn.prepareStatement("UPDATE eca_attendance SET deletestatus = 1 WHERE ecaid = ? and memberid = ?");
            pstmt.setInt(i++, ecaID);
            pstmt.setInt(i++, memberid);
            pstmt.executeUpdate();
            
            
            return true;
        }
        catch(Exception e){
            System.out.println(e);
            return false;
        }
    }
    
    public boolean deleteMember(Member member){
        try{
            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            
            PreparedStatement pstmt = conn.prepareStatement("DELETE FROM member WHERE memberid = ?");
            
            pstmt.setInt(i++, member.getMemberid());
            pstmt.executeUpdate();
            
            return true;
        }catch(Exception e){
        return false;
        }
    
    }

public boolean addMember(Member member){
        try{

            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            
            PreparedStatement pstmt = conn.prepareStatement("INSERT INTO member(memberid, username, password, firstname, lastname, batch, position) VALUES(?,?,?,?,?,?,?)");
            pstmt.setInt(i++, member.getMemberid());
            pstmt.setString(i++, member.getUsername());
            pstmt.setString(i++, member.getPassword());
            pstmt.setString(i++, member.getFirstname());
            pstmt.setString(i++, member.getLastname());
            pstmt.setString(i++, member.getBatch());
            pstmt.setString(i++, member.getPosition());
            
            pstmt.executeUpdate();

            return true;
        }
        catch(Exception e){
            System.out.print(e);
            return false;
        }
    }

public Member getMember(String memid)
     {
         Member mem = null;

         
         try{
             
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member where memberid = ? ");
             
             int i = 1;
             pstmt.setInt(i++, Integer.valueOf(memid));
             
             ResultSet rs = pstmt.executeQuery();
             
             
             while(rs.next())
             {
                 mem = new Member();
                 mem.setMemberid(rs.getInt("memberid"));
                 mem.setUsername(rs.getString("username"));
                 mem.setPassword(rs.getString("password"));
                 mem.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 mem.setFirstname(rs.getString("firstname"));
                 mem.setLastname(rs.getString("lastname"));
                 mem.setNickname(rs.getString("nickname"));
                 mem.setBirthdate(rs.getDate("birthdate"));
                 mem.setCourse(rs.getString("course"));
                 mem.setBatch(rs.getString("batch"));
                 mem.setPosition(rs.getString("position"));
                 mem.setPhone_number(rs.getString("phone_number"));
                 mem.setCell_number(rs.getString("cell_number"));
                 mem.setEmail(rs.getString("email"));
                 mem.setCgpa(rs.getFloat("cgpa"));
                 mem.setEca_points(rs.getFloat("eca_points"));
                 mem.setStatus(rs.getString("status"));
             }

             return mem;
         }        
         catch(Exception e){
         System.out.print(e);
            return mem;
         }
         finally{
             return mem;
         }
         
         
     }

public ArrayList<Member> getMemberName(String memname)
     {
         Member mem = null;
         ArrayList<Member> memberlist = null;
         
         try{
             memname = memname.toLowerCase();
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member where LCASE(firstname) LIKE ? OR LCASE(lastname) LIKE ? OR LCASE(nickname) LIKE ?");
             
             int i = 1;
             pstmt.setString(i++, "%" + memname + "%");
             pstmt.setString(i++, "%" + memname + "%");
             pstmt.setString(i++, "%" + memname + "%");
             
             ResultSet rs = pstmt.executeQuery();
             
             memberlist = new ArrayList<Member>();
             
             while(rs.next())
             {
                 mem = new Member();
                 mem.setMemberid(rs.getInt("memberid"));
                 mem.setUsername(rs.getString("username"));
                 mem.setPassword(rs.getString("password"));
                 mem.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 mem.setFirstname(rs.getString("firstname"));
                 mem.setLastname(rs.getString("lastname"));
                 mem.setNickname(rs.getString("nickname"));
                 mem.setBirthdate(rs.getDate("birthdate"));
                 mem.setCourse(rs.getString("course"));
                 mem.setBatch(rs.getString("batch"));
                 mem.setPosition(rs.getString("position"));
                 mem.setPhone_number(rs.getString("phone_number"));
                 mem.setCell_number(rs.getString("cell_number"));
                 mem.setEmail(rs.getString("email"));
                 mem.setCgpa(rs.getFloat("cgpa"));
                 mem.setEca_points(rs.getFloat("eca_points"));
                 mem.setStatus(rs.getString("status"));
                 memberlist.add(mem);
             }

             return memberlist;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberlist;
         }
         finally{
             return memberlist;
         }
         
         
     }

     public ArrayList<Member> getMemberID(int memid)
     {
         Member mem = null;
         ArrayList<Member> memberlist = null;
         
         try{
             ConnectionFactory myFactory = ConnectionFactory.getFactory();
             Connection conn = myFactory.getConnection();
             PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM member where memberid LIKE ?");
             
             int i = 1;
             pstmt.setString(i++, "%" + memid + "%");
             
             ResultSet rs = pstmt.executeQuery();
             
             memberlist = new ArrayList<Member>();
             
             while(rs.next())
             {
                 mem = new Member();
                 mem.setMemberid(rs.getInt("memberid"));
                 mem.setUsername(rs.getString("username"));
                 mem.setPassword(rs.getString("password"));
                 mem.setFullname(rs.getString("firstname") + " " + rs.getString("lastname"));
                 mem.setFirstname(rs.getString("firstname"));
                 mem.setLastname(rs.getString("lastname"));
                 mem.setNickname(rs.getString("nickname"));
                 mem.setBirthdate(rs.getDate("birthdate"));
                 mem.setCourse(rs.getString("course"));
                 mem.setBatch(rs.getString("batch"));
                 mem.setPosition(rs.getString("position"));
                 mem.setPhone_number(rs.getString("phone_number"));
                 mem.setCell_number(rs.getString("cell_number"));
                 mem.setEmail(rs.getString("email"));
                 mem.setCgpa(rs.getFloat("cgpa"));
                 mem.setEca_points(rs.getFloat("eca_points"));
                 mem.setStatus(rs.getString("status"));
                 memberlist.add(mem);
             }

             return memberlist;
         }        
         catch(Exception e){
         System.out.print(e);
            return memberlist;
         }
         finally{
             return memberlist;
         }
         
         
     }
     
public boolean editMember(Member member){
        try{

            ConnectionFactory myFactory = ConnectionFactory.getFactory();
            Connection conn = myFactory.getConnection();
            int i = 1;
            
            PreparedStatement pstmt = conn.prepareStatement("UPDATE member SET username = ?, password = ?, firstname = ?, lastname = ?, nickname = ?, birthdate = ?, course = ?, batch = ?, cgpa = ?, position = ?,  eca_points = ?, phone_number = ?, cell_number = ?, email = ?, status = ? WHERE memberid = ?");
            
            pstmt.setString(i++, member.getUsername());
            pstmt.setString(i++, member.getPassword());
            pstmt.setString(i++, member.getFirstname());
            pstmt.setString(i++, member.getLastname());
            pstmt.setString(i++, member.getNickname());
            pstmt.setDate(i++, member.getBirthdate());
            pstmt.setString(i++, member.getCourse());
            pstmt.setString(i++, member.getBatch());
            pstmt.setFloat(i++, member.getCgpa());
            pstmt.setString(i++, member.getPosition());
            pstmt.setFloat(i++, member.getEca_points());
            pstmt.setString(i++, member.getPhone_number());
            pstmt.setString(i++, member.getCell_number());
            pstmt.setString(i++, member.getEmail());
            pstmt.setString(i++, member.getStatus());
            pstmt.setInt(i++, member.getMemberid());
            
            pstmt.executeUpdate();

            return true;
        }
        catch(Exception e){
            return false;
        }
    }
}
